How to unlock the Super User Account on OAS 10g (on Windows server) ?

Question :
How to unlock the Super User Account on OAS 10g (Windows server) ?

Answer:
Connect to OAS server
from Oracle home directory issue following statement:

oidpasswd connect='oas db name' unlock_su_acct=true

where 'oas db name' is OAS'es database name

Change super user's password by :
log-in to Oracle Directory Manager

go to

Entry Management >> dc=domain >> dc=company >> cn=users >> cn='super user' >> properties >> orclpassword

Oracle AS 10g : how to change schema account from EXPIRED(GRACE) to OPEN ?

Question :
Oracle AS 10g : how to change schema account from EXPIRED(GRACE) to OPEN ?

Answer :
1. find all users with account status equals EXPIRED(GRACE) 
  SELECT s.username,
        s.password
    FROM dba_users s
   WHERE s.account_status LIKE '%GRACE%'
     AND s.account_status NOT LIKE '%LOCKED%'

2. Using values from previous select statement, issue following commands for each user:

alter user username profile DEFAULT;
alter user username identified by values 'password'

BEGIN
  FOR u_rec IN (SELECT s.username,
                       s.password
                  FROM dba_users s
                 WHERE s.account_status LIKE '%GRACE%'
                   AND s.account_status NOT LIKE '%LOCKED%')
  LOOP
    EXECUTE immediate 'alter user '||u_rec.username||' profile DEFAULT';
    EXECUTE immediate 'alter user '||u_rec.username||' IDENTIFIED BY VALUES '''|| u_rec.password ||'''';
  END LOOP;
END;

Database Links: Troubleshooting ORA-2085 "database link %s connects to %s"

  

To explain database link naming rules in an effort to avoid the error ORA-2085 "database link %s connects to %s"

When the source database initialization parameter GLOBAL_NAMES is set to true, the database link name must match the target database global name as it exists in the GLOBAL_NAME view in the data dictionary.

The GLOBAL_NAME can be determined by logging in to the database with system privileges and issuing the following command:

SQL>Select * from global_name;

Additionally, if you do not specify the domain portion of the dblink name in the create statement, Oracle automatically qualifies the link name with the domain of the SOURCE database global name view.

Check the contents of ALL_DB_LINKS for the fully qualified link name.

For example, if you defined a database link in PROD.ORACLE.COM to connect to target instance TEST.WORLD.COM in the following manner:

SQL>Create public database link TEST connect to userid identified by password using test;

SQL>select * from tablename@TEST;

This select would yield the following error:

ORA-2085 "database link TEST.ORACLE.COM connects to TEST.WORLD.COM"

The correct syntax for defining the link would be:

SQL>Create public database link TEST.WORLD.COM connect to userid identified by password using test;

SQL>select * from tablename@TEST.WORLD.COM;

Would yield desired result.

It is possible to alter the GLOBAL_NAME table so that the domain portion of both SOURCE and TARGET global names are identical. This would eliminate the need to include the domain in the create database link statement.

In the above example, we could alter the GLOBAL_NAME of TEST.WORLD.COM in the following manner:

Login to TEST with system privileges and issue:

SQL>alter database rename global_name to TEST.ORACLE.COM;

Now, the create database link statement could also be changed.

Login to PROD.

SQL>create public database link TEST connect to userid identified by password using test;

A database link would be defined in ALL_DB_LINKS as TEST.ORACLE.COM.

SQL>select * from tablename@TEST;

This would yield the desired result.

ORA-12154 ORA-12162 calling SQL*Loader / SQL*Plus

Problem Description 

Running SQL*Loader as: 

sqlload userid=... control=... data=... log=... 

HOSTSTR logical has been set to same value as your connection string but without domain name. 

When you have specified connect string (ie. SCOTT/TIGER@DATABASE) but no domain you receive these errors: 

SQL*Loader-704: Internal error: ulconnect: OCIServerAttach [0] 
ORA-12154: TNS:could not resolve service name 

When you have not specified connect string (ie. SCOTT/TIGER) you receive these errors: 

SQL*Loader-704: Internal error: ulconnect: OCIServerAttach [0] 
ORA-12162: TNS:service name is incorrectly specified 

Your sqlnet.ora has: 

names.default_domain entry = world 

The syntax in your tnsnames.ora entry is correct. 

Your entry in tnsnames.ora does not include the .WORLD extension (default domain from sqlnet.ora). 

Solution Description 

Specify the .WORLD in your tnsnames.ora and also in your connect string. This will remove the error. 

Also, ensure you are not hitting [BUG:893290].   

Source : Oracle Metalink Note:116852.1

ORA-29540: class oracle/plsql/net/TCPConnection does not exist

Problem Description

When attempting to run the packages UTL_SMTP, and UTL_TCP, an error is returned indicating that the class oracle/plsql/net/TCPConnection does not exist.  This class, along with others that support SQLJ functionality, should normally be loaded when the "initjvm.sql" script is run. 

However, that script has already been run successfully, to completion.

ORA-29540: class oracle/plsql/net/TCPConnection does not exist

Solution Description

The initjvm.sql script (located in the ORACLE_HOME/javavm/install directory) is run automatically during normal installation of the database, except during custom installation.  In any event, thescript can be run manually (as SYS) at a later time. When this script is run, approximately 8000+ java classes are loaded into the database. 

Unfortunately, there is another script, initplsj.sql (located in ORACLE_HOME/rdbms/admin) which is supposed to be called by the initjvm.sql script. This does not occur. To fix the problem, runthe initplsj.sql script (as SYS).  You will note that 200+ classes get loaded into the database as a result of this script. 

This should then allow you to use the UTL_SMTP and UTL_TCP packages.

This script also needs to be run:

$ORACLE_HOME/javavm/install/init_security.sql

Source: Oracle Metalink

Oracle Reports background engine stopping

In Forms 6i when a report is run, a background process called Reports Background Engine is created. Sometimes we want  this process to be terminated automatically when a report is completed and we exit from report.

Here is the PL/SQL code for this:

DECLARE
  pl_id ParamList;
BEGIN
  pl_id := Create_Parameter_List('dummy');
  Add_Parameter(pl_id,'ORACLE_SHUTDOWN',TEXT_PARAMETER,'Yes');
  Add_Parameter(pl_id,'PARAMFORM',TEXT_PARAMETER,'NO');
  Run_product(REPORTS,'<>',SYNCHRONOUS,     RUNTIME,FILESYSTEM,pl_id,NULL);
END;

How to Troubleshoot Distributed Transactions ? (ORA-2019, ORA-2058, ORA-2068, ORA-2050, FAILED DISTRIBUTED TRANSACTIONS)

How to Troubleshoot Distributed Transactions ? 

Database "A" has init.ora parameter 
db_domain incorrectly specified as ".WORLD", that causes the init.ora parameter "service_names" to be "A..WORLD". 

A select from GLOBAL_NAMES shows "A.WORLD". Database "B" has init.ora parameters of "db_domain=WORLD" and SERVICE_NAMES=B.WORLD". 

Select from GLOBAL_NAME shows "B.WORLD". The table synonyms referring to the remote tables work for both databases to data manipulation. But, the synonyms on database "A" cannot be used to describe the tables on "B" (ORA-02019). 

Can I safely change the "A" init.ora parameter from "db_domain=.WORLD" to "db_domain=WORLD"? I know from my test databases that that parameter inhibits the describe. 

Set GLOBAL_NAMES=FALSE in the init.ora and restart the database.

The syntax is as follows: 
Create public database link  
Connect to uid identified by pw 
Using 'alias'; 

Ensure that global_names = true in the destination server's init.ora file. If the global name = true parameter is set in the init.ora, check the value of the db_name and db_domain parameters in that same init.ora file. Log into the destination server via sqlplus and do a SELECT * FROM GLOBAL_NAME. This value should match the db_name specified in the init.ora file. If the global name = true parameter exists in the init.ora file, then the name of the dblink must be either that databases sid or global dbname. 

How the TNSNAMES.ORA file is used

Here is to explain the behavior how tnsnames.ora and sqlnet.ora file work together. 


1. The default domain in the Sqlnet file is used for a default purpose only. 

CASE 1: 
in tnsnames.ora 
des6i.world = (....)  

in sqlnet.ora file 
names.default_domain = world 

So, in sqlplus log screen, the following can be typed in: scott/tiger@des6i 
Note the fully qualified des6iworld would not be typed in, because it  
will be automatically appended to the end of the connect string specified. 

CASE 2: 
in tnsnames.ora 
des6i.world = (....)  
CASE.NW.NOS.ORALCE.COM = (....) 

in sqlnet.ora file 
names.default_domain = world 

What would happen in the case where multiple domain have been defined in the  
tnsnames.ora but with only one sqlnet.ora?  Just remember, the key is what is  
defined in the TNSNAMES.ORA file, sqlnet.ora is for convenience.   
The entire connect string can be typed in. 

In sqlplus log screen, just type in :  
scott/tiger@des6i  <--- should again work 
scott/tiger@des6i.world  <--- should again work 
scott/tiger@CASE  <--- should *NOT* work, because in fact it will be CASE.world 
scott/tiger@CASE.NW.NOS.ORALCE.COM    <--- should work 

What is being said is that if the domain is added after the name when loginning 
as defined in the tnsnames.ora file, then don't use the default domain that is  
defined in the sqlnet.ora file. 

CASE 3: 
In fact a sqlnet.ora file is not really needed for the purposes of login 

E.g., in tnsnames.ora 
des6i  = (....)   <--- note here, no domain 
CASE.NW.NOS.BOEING.COM = (....) 

no sqlnet.ora file exists anywhere. 

In sqlplus log screen, just type in :  
scott/tiger@des6i  <--- should again work 
scott/tiger@CASE.NW.NOS.BOEING.COM    <--- should work 
scott/tiger@CASE   <--- should *NOT* work 
scott/tiger@des6i.world  <--- should *NOT* work 


Source : Oracle Metalink Note:201316.1